﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Threading;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;

namespace HisGL
{    
    public sealed class DbSession : IDisposable
    {
        private const string ConnectionName = "His";
        private const string ConnProviderName = "System.Data.SqlClient";        
        
        [ThreadStatic]
        private static DbSession currentSession;
        private string connectionName;
        private string providerName;
        private string connectionString;
        private bool useTransaction;
        private int commandTimeout;
        private bool commitTrans;
        private string fixParm="@"; //参数前缀sqlserver为@，oracle 为 :

        private DbConnection connection = null;
        private DbTransaction dbtrans = null;
        private static DbProviderFactory dbfactory = null;

        private StringBuilder sbSQL = null;
        private List<object> lsParaVals = null;

        private List<DbCommand> lsDbCommand = null;

        private DbSession()
        {
            getDeafaultSession();
        }

        /// <summary>
        /// 创建数据库访问1
        /// </summary>
        /// <param name="connectionName">连接名</param>
        public DbSession(string connectionName)
            : this(ConnProviderName, connectionName, false)
        {
        }

        /// <summary>
        /// 创建数据库访问2
        /// </summary>
        /// <param name="providerName">默认为：System.Data.SqlClient</param>
        /// <param name="connectionName">连接名</param>
        public DbSession(string providerName,string connectionName)
            : this(providerName, connectionName, false)
        {
            this.providerName = providerName;
        }

        /// <summary>
        /// 创建数据库访问3
        /// </summary>
        /// <param name="connectionName">连接名</param>
        /// <param name="useTransaction">是否启用事务</param>
        public DbSession(string providerName, string connectionName, bool useTransaction)
          : this(useTransaction, providerName, ConfigurationManager.ConnectionStrings[connectionName].ToString())
        {

        }

        public DbSession(bool useTransaction,string providerName, string connectionString)
        {
          this.useTransaction = false;
          this.commitTrans = true;
          this.commandTimeout = 30;
          this.providerName = providerName;
          this.connectionString = connectionString;
          Thread.BeginThreadAffinity();
          this.connection = CreateConnection(providerName, connectionString);

          this.sbSQL = new StringBuilder();
          this.lsParaVals = new List<object>();

          if (providerName.StartsWith("Oracle"))
          {
              this.fixParm = ":";
          }

          currentSession = this;

          this.useTransaction = useTransaction;
          if (this.useTransaction)
          {
              this.lsDbCommand = new List<DbCommand>();             
              //this.dbtrans = connection.BeginTransaction();
              this.commitTrans = false;
          }

        }

        /// <summary>
        /// 获取当前访问连接
        /// </summary>
        public static DbSession Current
        {
            get
            {
                DbSession session = (currentSession != null) ? currentSession : null;
                if (session == null)
                {
                    getDeafaultSession();
                    session = currentSession;
                }
                if (session == null)
                {
                    throw new Exception("No valid connection");
                }
                return session;
            }
        }

        private static void getDeafaultSession()
        {
            if (currentSession == null)
            {
                string ky = ConfigurationManager.ConnectionStrings[ConnectionName].ToString();
                if (ky != null)
                {
                    currentSession = new DbSession(ky);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        private static DbConnection CreateConnection(string providerName, string connectionString)
        {
            dbfactory = DbProviderFactories.GetFactory(providerName);

            DbConnection dbconn = dbfactory.CreateConnection();

            dbconn.ConnectionString = connectionString;
            dbconn.Open();
            return dbconn;

        }

        /// <summary>
        /// 查询数据库
        /// </summary>
        /// <param name="sql">SELECT SQL</param>
        /// <returns></returns>
        public DataTable Query(string sql)
        {
            return Query(this.GetDbSqlCommand(sql));
        }

        /// <summary>
        /// SQL参数化查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paravals"></param>
        /// <returns></returns>
        public DataTable Query(string sql, params object[] paravals)
        {          
            return Query(this.GetDbSqlCommand(sql, paravals));
        }

        /// <summary>
        /// 通过AddSql添加sql后查询
        /// </summary>
        /// <returns></returns>
        public DataTable Query()
        {
            return Query(this.GetDbSqlCommand(sbSQL.ToString(), lsParaVals.ToArray())); 
        }

        /// <summary>
        /// 通过DbCommand对象查询
        /// </summary>
        /// <param name="dbCommand"></param>
        /// <returns></returns>
        public DataTable Query(DbCommand dbCommand)
        {
            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();

            dbDataAdapter.SelectCommand = dbCommand;

            DataTable dataTable = new DataTable();

            //dbDataAdapter.FillSchema(dataTable, SchemaType.Mapped);
            
            dbDataAdapter.Fill(dataTable);

            return dataTable;
        }

        /// <summary>
        ///直接执行SQL语句,
        ///建议使用Execute(string sql, params object[] paravals) 
        ///可以防止sql注入
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Execute(string sql)
        {
            return Execute(this.GetDbSqlCommand(sql));

        }

        public int Execute(string sql, params object[] paravals)
        {
            return Execute(this.GetDbSqlCommand(sql, paravals));
        }

        /// <summary>
        /// 通过AddSql添加sql后执行
        /// </summary>
        /// <returns></returns>
        public int Execute()
        {
            return Execute(this.GetDbSqlCommand(sbSQL.ToString(), lsParaVals.ToArray()));
        }

        public int Execute(DbCommand dbCommand)
        {
            if (this.useTransaction)
            {
                this.lsDbCommand.Add(dbCommand);                
                //dbCommand.Transaction = this.dbtrans;
                return 1; 
            }
            else
            {
                return dbCommand.ExecuteNonQuery();
            }
            
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransaction()
        {
            if (((this.lsDbCommand.Count >0) && (this.connection != null)) && (this.connection.State != ConnectionState.Closed))
            {
                this.dbtrans = connection.BeginTransaction();
                foreach (DbCommand dbCommand in this.lsDbCommand)
                {
                    dbCommand.Transaction = this.dbtrans;
                    dbCommand.ExecuteNonQuery();
                }
              
                this.dbtrans.Commit();
                this.commitTrans = true;
                this.dbtrans.Dispose();
                this.dbtrans = null;
                this.lsDbCommand.Clear();
            }
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTransaction()
        {
            if (((this.dbtrans != null) && (this.connection != null)) && (this.connection.State != ConnectionState.Closed))
            {
                this.dbtrans.Rollback();
                this.commitTrans = true;
                this.dbtrans.Dispose();
                this.dbtrans = null;
                this.lsDbCommand.Clear();
            }

        }

        internal void Close()
        {
            if ((this.connection != null) && (this.connection.State != ConnectionState.Closed))
            {
                if (!((this.dbtrans == null) || this.commitTrans))
                {
                    this.dbtrans.Rollback();

                }
                this.connection.Close();
                this.connection.Dispose();
                this.connection = null;
            }
        }

        /// <summary>
        /// 清除SQL
        /// </summary>
        public void ClearSql()
        {
            sbSQL.Length = 0;
            lsParaVals.Clear();
        }

        /// <summary>
        /// 参数化添加sql
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paravals"></param>
        public void AddSql(string sql, params object[] paravals)
        {
            string[] parameters = new string[paravals.Length];
            for (int i = 0; i < paravals.Length; i++)
            {
                parameters[i] = fixParm+"p" + (i + lsParaVals.Count).ToString()+"p";
            }
            sbSQL.Append(string.Format(sql, parameters));
            for (int i = 0; i < paravals.Length; i++)
            {
                lsParaVals.Add(paravals[i]);
            }
        }

        public void AddWhere(Dictionary<string, object> sqlWheres)
        {
            foreach (var sw in sqlWheres)
            {
                AddSql(" " + sw.Key + " ", sw.Value);
            }
        }
        public int RecordCount()
        {
            string sql = String.Format(@"SELECT COUNT(1) AS DbSessionRecordCount FROM ({0}) A ", sbSQL.ToString());
            return RecordCount(sql, "DbSessionRecordCount");
        }

        public int RecordCount(string formatsql, string sCountField)
        {
            DataTable dt = Query(this.GetDbSqlCommand(formatsql, lsParaVals.ToArray()));
            return Convert.ToInt32(dt.Rows[0][sCountField].ToString());

        }

        public DataTable FetchData(int iCurPage, int iLimit)
        {
            return FetchData(iCurPage, iLimit, "_ROW");
        }

        public DataTable FetchData(int iCurPage, int iLimit, string sRowField)
        {
            string sql = String.Format(@"SELECT * FROM ({0}) A WHERE {3}>={1} AND {3}<={2} ORDER BY {3}",
               sbSQL.ToString(), (iCurPage - 1) * iLimit + 1, iCurPage * iLimit, sRowField);

            return Query(this.GetDbSqlCommand(sql, lsParaVals.ToArray()));
        }

        public DataTable FetchData(Dictionary<string, object> dicWheres, int iCurPage, int iLimit,ref int iSumCount)
        {
            return FetchData(dicWheres, iCurPage, iLimit, ref iSumCount,"_ROW");
        }

        public DataTable FetchData(Dictionary<string, object> dicWheres,
            int iCurPage, int iLimit, ref int iSumCount, string sRowField)
        {
            AddWhere(dicWheres);
            
            if (iCurPage > 0 && iLimit > 0)
            {
                iSumCount = RecordCount();
            }
            
            string sql = String.Format(@"SELECT * FROM ({0}) A WHERE {3}>={1} AND {3}<={2} ORDER BY {3}",
               sbSQL.ToString(), (iCurPage - 1) * iLimit + 1, iCurPage * iLimit, sRowField);

            return Query(this.GetDbSqlCommand(sql, lsParaVals.ToArray()));
        }
        

        public string ToSqlString()
        {
            string sVal = "";
            string sSql = sbSQL.ToString();
            for (int i = 0; i < lsParaVals.Count; i++)
            {
                if(lsParaVals[i] == null)
                {
                    sVal = "null";
                }
                else if (lsParaVals[i].GetType() == typeof(string))
                {
                    sVal = "'" + lsParaVals[i].ToString() + "'";
                }
                else
                {
                    sVal = lsParaVals[i].ToString();
                }

                sSql = sSql.Replace(fixParm+"p" + i.ToString()+"p", sVal);
            }
            return sSql;
        }

        private DbCommand GetDbSqlCommand(string formatsql)
        {
            DbCommand dbCommand = connection.CreateCommand();
            dbCommand.Connection = connection;
            dbCommand.CommandText = formatsql;
            dbCommand.CommandTimeout = this.commandTimeout;

            if (this.useTransaction)
            {
                //dbCommand.Transaction = this.dbtrans;
            }

            return dbCommand;
        }

        private DbCommand GetDbSqlCommand(string formatsql, params object[] paravals)
        {
            string[] parameters = new string[paravals.Length];
            for (int i = 0; i < paravals.Length; i++)
            {
                parameters[i] = fixParm + "p" + i.ToString() + "p";
            }
            DbCommand dbCommand = connection.CreateCommand();
            dbCommand.Connection = connection;
            dbCommand.CommandText = string.Format(formatsql, parameters);
            //dbCommand.CommandType = CmdType;
            dbCommand.CommandTimeout = this.commandTimeout;

            if(this.useTransaction)
            {
                dbCommand.Transaction = this.dbtrans;
            }

            for (int i = 0; i < paravals.Length; i++)
            {
                if (paravals[i] == null)
                {
                    dbCommand.Parameters.Add(new SqlParameter("p" + i.ToString() + "p", DBNull.Value));
                }
                else
                {
                    dbCommand.Parameters.Add(new SqlParameter("p" + i.ToString() + "p", paravals[i]));
                } 
            }


            return dbCommand;
        }

        /// <summary>
        /// 释放
        /// </summary>
        public void Dispose()
        {
            Close();
        }
    }

}